-- generate t-sql to revoke all permissions on all db objects from all db users
SET IMPLICIT_TRANSACTIONS OFF
SELECT tsql_command='REVOKE EXECUTE ON [' + routine_schema + '].[' + routine_name + '] FROM [' + [name] + ']'
  FROM information_schema.routines CROSS JOIN sys.database_principals
  WHERE [type] IN ('S','U') AND 4 < principal_id AND principal_id < 16384
UNION
SELECT tsql_command='REVOKE SELECT,INSERT,UPDATE,DELETE ON [' + table_schema + '].[' + table_name + '] FROM [' + [name] + ']'
  FROM information_schema.tables CROSS JOIN sys.database_principals
  WHERE [type] IN ('S','U') AND 4 < principal_id AND principal_id < 16384
ORDER BY tsql_command
GO


-- Remove Ownership from Default Schemas
ALTER AUTHORIZATION ON SCHEMA::db_accessadmin TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_backupoperator TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_datareader TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_datawriter TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_ddladmin TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_denydatareader TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_denydatawriter TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_securityadmin TO dbo;

-- Drop Non-System Schemas
SELECT 'DROP SCHEMA [' + name +']' FROM sys.schemas WHERE 4 < schema_id AND schema_id < 16384;

-- Remove All Members from All Roles
WITH
	r AS (SELECT principal_id, name FROM sys.database_principals),
	m AS (SELECT principal_id, name FROM sys.database_principals)
SELECT 'EXECUTE sp_droprolemember @rolename = ''' + r.name + ''', @membername = ''' + m.name + ''''
  FROM sys.database_role_members
	INNER JOIN r ON role_principal_id = r.principal_id 
	INNER JOIN m ON member_principal_id = m.principal_id 
  WHERE 1 < member_principal_id
UNION ALL

-- Drop All Database Roles
SELECT 'DROP ROLE [' + name +']' FROM sys.database_principals WHERE type = 'R' AND 4 < principal_id AND principal_id < 16384
UNION ALL

-- Drop All Database Users
SELECT 'DROP USER [' + name +']' FROM sys.database_principals WHERE type IN ('G','S','U') AND 4 < principal_id AND principal_id < 16384

GO